package com.groupscore.score.dao;

import com.groupscore.score.pojo.Score;
import com.groupscore.score.pojogroup.GroupScore;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;

import java.util.List;
import java.util.Map;

public interface ScoreDao extends JpaRepository<Score, String> {

    /**
     * 小组得分从高到低
     */
    @Query(value = "select SUM(score) as score, group_id as groupId from score GROUP BY group_id ORDER BY score desc", nativeQuery = true)
    public List<Map> groupScore();


    /**
     * 根据登录人id查询所打分的各个小组的各项总分
     */
    @Query(value = "select group_id as groupId,score  from score where user_id = ?1 ORDER BY group_id desc", nativeQuery = true)
    public List<Map> groupScoreByUserId(String userId);

    /**
     * 小组所有得分从高到低
     */
    @Query(value = "select sum(score) as score, u.nikename as name, u.group_id from score s, user u where s.user_id = u.id GROUP BY user_id ORDER BY u.group_id", nativeQuery = true)
    public List<Map> allScore();

    /**
     * 个人得分，从高到低
     */
    @Query(value = "SELECT u.nikename as name,SUM(score) as score from personal_score p, user u where p.user_id = u.id GROUP BY user_id", nativeQuery = true)
    public List<Map> allPersonalScore();
}
